# -*- coding: utf-8 -*-
# @Author : 17
# @Time    : 2024/4/24 13:57
# @File    : mysql_conn.py
import pymysql
from warnings import filterwarnings
from sshtunnel import SSHTunnelForwarder

# 忽略 Mysql 告警信息
filterwarnings("ignore", category=pymysql.Warning)


class MysqlDB(object):

    def __init__(self):
        self.server = SSHTunnelForwarder(
            ssh_address_or_host=('192.168.6.21', 22),
            ssh_username='root',
            ssh_password='123456a',
            remote_bind_address=('127.0.0.1', 3306)
        )

        # 启动隧道服务
        self.server.start()

        mysql_config = {
            'user': 'root',
            'passwd': '123456',
            'host': '127.0.0.1',
            'port': self.server.local_bind_port,
            'db': 'zentao'
        }

        # 连接数据库
        self.mysql = pymysql.connect(**mysql_config)
        self.cursor = self.mysql.cursor(cursor=pymysql.cursors.DictCursor)

    def __del__(self):
        try:
            self.cursor.close()  # 关闭游标
            self.mysql.close()   # 关闭连接
            self.server.close()  # 关闭服务
        except Exception as e:
            print(f"关闭连接时发生异常：{e}")

    def fetch_one(self, sql):
        # 执行SQL
        self.cursor.execute(sql)
        # 查看结果
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql):
        # 执行SQL
        self.cursor.execute(sql)
        # 查看结果
        result = self.cursor.fetchall()
        return result

    def execute_sql(self, sql):
        # 执行SQL
        self.cursor.execute(sql)
        # 提交
        self.mysql.commit()
        # 返回影响行数
        result = "受影响的行数：{}".format(self.cursor.rowcount)
        return result



